package cn.caipengbo.utils.jdbc;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.*;
import java.util.Date;


/**
 * JDBC工具类<br/>
 * 配置文件(dbconfig.properties)格式(以mysql为例)：<br>
 * db_url=jdbc:mysql://localhost:3306/数据库名<br>
 * db_driver=com.mysql.jdbc.Driver<br>
 * db_username=root(用户名)<br>
 * db_password=密码<br>
 * 配置文件地址：src(WEB-INF-classes)目录下<br><br>
 * 如有bug，请联系作者——蔡鹏博-mail@caipengbo.cn
 */
public class DbUtil {

    private static Connection con = null;

    /**
     * 打开与数据库的链接
     * 需在config-db.properties文件中配置参数,注意配置文件的路径（在src下）
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     * @throws IOException
     */
    public static void openConnection() throws SQLException, ClassNotFoundException, IOException {
        if (null == con || con.isClosed()) {
            //String path = ClassLoader.getSystemClassLoader().getResource(".").getPath() + "/dbconfig.properties";
            InputStream in = DbUtil.class.getClassLoader().getResourceAsStream("dbconfig.properties");
            //FileInputStream file = new FileInputStream(path);
            Properties props = new Properties();
            props.load(in);
            Class.forName(props.getProperty("db_driver"));
            String url = props.getProperty("db_url");
            String username = props.getProperty("db_username");
            String password = props.getProperty("db_password");
            con = DriverManager.getConnection(url,username,password);
        }
    }

    /**
     * 关闭与数据库的链接
     * @throws SQLException
     */
    public static void closeConnection() throws SQLException {
        try {
            if (null != con)
                con.close();
        } finally {
            con = null;
            System.gc();
        }
    }

    /**
     * 查询数据库
     * @param sql
     * @return List<Map<String, Object>>
     * @throws SQLException
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    public static List<Map<String, Object>> queryMapList(String sql) throws SQLException,
            InstantiationException, IllegalAccessException {
        List<Map<String, Object>> lists = new ArrayList<Map<String, Object>>();
        Statement preStmt = null;
        ResultSet rs = null;
        try {
            preStmt = con.createStatement();
            rs = preStmt.executeQuery(sql);
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            while (null != rs && rs.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                for (int i = 0; i < columnCount; i++) {
                    String name = rsmd.getColumnName(i + 1);
                    Object value = rs.getObject(name);
                    map.put(name, value);
                }
                lists.add(map);
            }
        } finally {
            if (null != rs)
                rs.close();
            if (null != preStmt)
                preStmt.close();
        }
        return lists;
    }

    /**
     * 使用PreparedStatement的查询语句
     * @param sql 预编译的sql，利用？代表参数
     * @param params 任意数目的参数
     * @return List<Map<String, Object>>
     * @throws SQLException
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    public static List<Map<String, Object>> queryMapList(String sql, Object... params)
            throws SQLException, InstantiationException, IllegalAccessException {
        List<Map<String, Object>> lists = new ArrayList<Map<String, Object>>();
        PreparedStatement preStmt = null;
        ResultSet rs = null;
        try {
            preStmt = con.prepareStatement(sql);
            for (int i = 0; i < params.length; i++)
                preStmt.setObject(i + 1, params[i]);// 下标从1开始
            rs = preStmt.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            while (null != rs && rs.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                for (int i = 0; i < columnCount; i++) {
                    String name = rsmd.getColumnName(i + 1);
                    Object value = rs.getObject(name);
                    map.put(name, value);
                }
                lists.add(map);
            }
        } finally {
            if (null != rs)
                rs.close();
            if (null != preStmt)
                preStmt.close();
        }
        return lists;
    }

    /**
     * 查询数据库
     * @param sql
     * @param beanClass
     * @param <T>
     * @return Bean类的List
     * @throws SQLException
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    public static <T> List<T> queryBeanList(String sql, Class<T> beanClass) throws SQLException,
            InstantiationException, IllegalAccessException {
        List<T> lists = new ArrayList<T>();
        Statement stmt = null;
        ResultSet rs = null;
        Field[] fields = null;
        try {
            stmt = con.createStatement();
            rs = stmt.executeQuery(sql);
            fields = beanClass.getDeclaredFields();
            for (Field f : fields)
                f.setAccessible(true);
            while (null != rs && rs.next()) {
                T t = beanClass.newInstance();
                for (Field f : fields) {
                    String name = f.getName();
                    try {
                        Object value = rs.getObject(name);
                        setValue(t, f, value);
                    } catch (Exception e) {
                    }
                }
                lists.add(t);
            }
        } finally {
            if (null != rs)
                rs.close();
            if (null != stmt)
                stmt.close();
        }
        return lists;
    }

    /**
     * 使用PreparedStatement的查询,需要添加参数
     * @param sql
     * @param beanClass
     * @param params ？占位符参数
     * @param <T>
     * @return Bean类的Lits
     * @throws SQLException
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    public static <T> List<T> queryBeanList(String sql, Class<T> beanClass, Object... params)
            throws SQLException, InstantiationException, IllegalAccessException {
        List<T> lists = new ArrayList<T>();
        PreparedStatement preStmt = null;
        ResultSet rs = null;
        Field[] fields = null;
        try {
            preStmt = con.prepareStatement(sql);
            for (int i = 0; i < params.length; i++)
                preStmt.setObject(i + 1, params[i]);// 下标从1开始
            rs = preStmt.executeQuery();
            fields = beanClass.getDeclaredFields();
            for (Field f : fields)
                f.setAccessible(true);
            while (null != rs && rs.next()) {
                T t = beanClass.newInstance();
                for (Field f : fields) {
                    String name = f.getName();
                    try {
                        Object value = rs.getObject(name);
                        setValue(t, f, value);  //为Bean赋值
                    } catch (Exception e) {
                    }
                }
                lists.add(t);
            }
        } finally {
            if (null != rs)
                rs.close();
            if (null != preStmt)
                preStmt.close();
        }
        return lists;
    }

    public static <T> List<T> queryBeanList(String sql, IResultSetCall<T> qdi) throws SQLException {
        List<T> lists = new ArrayList<T>();
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = con.createStatement();
            rs = stmt.executeQuery(sql);
            while (null != rs && rs.next())
                lists.add(qdi.invoke(rs));
        } finally {
            if (null != rs)
                rs.close();
            if (null != stmt)
                stmt.close();
        }
        return lists;
    }

    public static <T> List<T> queryBeanList(String sql, IResultSetCall<T> qdi, Object... params)
            throws SQLException {
        List<T> lists = new ArrayList<T>();
        PreparedStatement preStmt = null;
        ResultSet rs = null;
        try {
            preStmt = con.prepareStatement(sql);
            for (int i = 0; i < params.length; i++)
                preStmt.setObject(i + 1, params[i]);
            rs = preStmt.executeQuery();
            while (null != rs && rs.next())
                lists.add(qdi.invoke(rs));
        } finally {
            if (null != rs)
                rs.close();
            if (null != preStmt)
                preStmt.close();
        }
        return lists;
    }

    /**
     * 查询数据库，获得Bean类一条数据
     * @param sql
     * @param beanClass
     * @param <T> Bean类.Class
     * @return 仅一条数据,若为空，返回null
     * @throws SQLException
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    public static <T> T queryBean(String sql, Class<T> beanClass) throws SQLException,
            InstantiationException, IllegalAccessException {
        List<T> lists = queryBeanList(sql, beanClass);
        if (lists.size() > 1)
            throw new SQLException("SqlError：获得数据超过一行！");
        if (lists.size() == 0 ) {
            return null;
        }
        return lists.get(0);
    }

    /**
     * 利用PreparedStatement,查询数据库，获得Bean类一条数据
     * @param sql
     * @param beanClass
     * @param params
     * @param <T>
     * @return 仅一条数据,若为空，返回null
     * @throws SQLException
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    public static <T> T queryBean(String sql, Class<T> beanClass, Object... params)
            throws SQLException, InstantiationException, IllegalAccessException {
        List<T> lists = queryBeanList(sql, beanClass, params);
        if (lists.size() > 1)
            throw new SQLException("SqlError：获得数据超过一行！");
        if (lists.size() == 0 ) {
            return null;
        }
        return lists.get(0);
    }


    /**
     * 查询某一具体列
     * @param sql
     * @param objClass 此列的 类.Class
     * @param <T>
     * @return 该列类型 的 List
     * @throws SQLException
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    public static <T> List<T> queryObjectList(String sql, Class<T> objClass) throws SQLException,
            InstantiationException, IllegalAccessException {
        List<T> lists = new ArrayList<T>();
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = con.createStatement();
            rs = stmt.executeQuery(sql);
            label: while (null != rs && rs.next()) {
                Constructor<?>[] constor = objClass.getConstructors();
                for (Constructor<?> c : constor) {
                    Object value = rs.getObject(1);
                    try {
                        lists.add((T) c.newInstance(value));
                        continue label;
                    } catch (Exception e) {
                    }
                }
            }
        } finally {
            if (null != rs)
                rs.close();
            if (null != stmt)
                stmt.close();
        }
        return lists;
    }

    /**
     * 使用PreparedStatement查询某一列
     * @param sql
     * @param objClass
     * @param params
     * @param <T>
     * @return 某一列的List
     * @throws SQLException
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    public static <T> List<T> queryObjectList(String sql, Class<T> objClass, Object... params)
            throws SQLException, InstantiationException, IllegalAccessException {
        List<T> lists = new ArrayList<T>();
        PreparedStatement preStmt = null;
        ResultSet rs = null;
        try {
            preStmt = con.prepareStatement(sql);
            for (int i = 0; i < params.length; i++)
                preStmt.setObject(i + 1, params[i]);
            rs = preStmt.executeQuery();
            label: while (null != rs && rs.next()) {
                Constructor<?>[] constor = objClass.getConstructors();
                for (Constructor<?> c : constor) {
                    String value = rs.getObject(1).toString();
                    try {
                        T t = (T) c.newInstance(value);
                        lists.add(t);
                        continue label;
                    } catch (Exception e) {
                    }
                }
            }
        } finally {
            if (null != rs)
                rs.close();
            if (null != preStmt)
                preStmt.close();
        }
        return lists;
    }

    /**
     * 查询某一列一行数据
     * @param sql
     * @param objClass
     * @param <T>
     * @return 一个对象(一个数据)，若为空，返回null
     * @throws SQLException
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    public static <T> T queryObject(String sql, Class<T> objClass) throws SQLException,
            InstantiationException, IllegalAccessException {
        List<T> lists = queryObjectList(sql, objClass);
        if (lists.size() > 1)
            throw new SQLException("SqlError：获得数据超过一行！");
        if (lists.size() == 0 ) {
            return null;
        }
        return lists.get(0);
    }

    /**
     * 使用PreparedStatement查询某一列一行。<br/>
     * Demo：<br/>String sql = "SELECT salary FROM employees t WHERE t.employee_id in (?)";<br/>
     * Double d = DbUtil.queryObject(sql, Double.class, 12);
     * @param sql
     * @param objClass
     * @param params
     * @param <T>
     * @return 一条数据，若为空，返回null
     * @throws SQLException
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    public static <T> T queryObject(String sql, Class<T> objClass, Object... params)
            throws SQLException, InstantiationException, IllegalAccessException {
        List<T> lists = queryObjectList(sql, objClass, params);
        if (lists.size() > 1)
            throw new SQLException("SqlError：获得数据超过一行！");
        if (lists.size() == 0 ) {
            return null;
        }
        return lists.get(0);
    }

    /**
     * 执行SQL语句（插入、更新、删除）
     * @param sql
     * @return
     * @throws SQLException
     */
    public static int execute(String sql) throws SQLException {
        Statement stmt = null;
        try {
            stmt = con.createStatement();
            return stmt.executeUpdate(sql);
        } finally {
            if (null != stmt)
                stmt.close();
        }
    }

    /**
     * 使用PreparedStatement执行SQL语句
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public static int execute(String sql, Object... params) throws SQLException {
        PreparedStatement preStmt = null;
        try {
            preStmt = con.prepareStatement(sql);
            for (int i = 0; i < params.length; i++)
                preStmt.setObject(i + 1, params[i]);// 下标从1开始
            return preStmt.executeUpdate();
        } finally {
            if (null != preStmt)
                preStmt.close();
        }
    }

    /**
     * 批量执行SQL语句
     * @param sqlList SQL语句List
     * @return
     * @throws SQLException
     */
    public static int[] executeAsBatch(List<String> sqlList) throws SQLException {
        return executeAsBatch(sqlList.toArray(new String[] {}));
    }

    /**
     * 批量执行SQL语句
     * @param sqlArray SQL String Array
     * @return
     * @throws SQLException
     */
    public static int[] executeAsBatch(String[] sqlArray) throws SQLException {
        Statement stmt = null;
        try {
            stmt = con.createStatement();
            for (String sql : sqlArray) {
                stmt.addBatch(sql);
            }
            return stmt.executeBatch();
        } finally {
            if (null != stmt) {
                stmt.close();
            }
        }
    }

    /**
     * 使用PreparedStatement批量执行SQL语句
     * @param sql
     * @param params 参数二维数组
     * @return
     * @throws SQLException
     */
    public static int[] executeAsBatch(String sql, Object[][] params) throws SQLException {
        PreparedStatement preStmt = null;
        try {
            preStmt = con.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                Object[] rowParams = params[i];
                for (int k = 0; k < rowParams.length; k++) {
                    Object obj = rowParams[k];
                    preStmt.setObject(k + 1, obj);
                }
                preStmt.addBatch();
            }
            return preStmt.executeBatch();
        } finally {
            if (null != preStmt) {
                preStmt.close();
            }
        }
    }
    //将结果集中各中类型的值转化成Java Bean 中各个字段（Field）对于的类型值
    private static <T> void setValue(T t, Field f, Object value) throws IllegalAccessException {
        // 还是以java数据类型为准绳
        if (null == value)
            return;
        String v = value.toString();
        String n = f.getType().getName();
        if ("java.lang.Byte".equals(n) || "byte".equals(n)) {
            f.set(t, Byte.parseByte(v));
        } else if ("java.lang.Short".equals(n) || "short".equals(n)) {
            f.set(t, Short.parseShort(v));
        } else if ("java.lang.Integer".equals(n) || "int".equals(n)) {
            f.set(t, Integer.parseInt(v));
        } else if ("java.lang.Long".equals(n) || "long".equals(n)) {
            f.set(t, Long.parseLong(v));
        } else if ("java.lang.Float".equals(n) || "float".equals(n)) {
            f.set(t, Float.parseFloat(v));
        } else if ("java.lang.Double".equals(n) || "double".equals(n)) {
            f.set(t, Double.parseDouble(v));
        } else if ("java.lang.String".equals(n)) {
            f.set(t, value.toString());
        } else if ("java.lang.Character".equals(n) || "char".equals(n)) {
            f.set(t, (Character) value);
        } else if ("java.lang.Date".equals(n)) {
            f.set(t, new Date(((java.sql.Date) value).getTime()));
        } else if ("java.lang.Timer".equals(n)) {
            f.set(t, new Time(((Time) value).getTime()));
        } else if ("java.sql.Timestamp".equals(n)) {
            f.set(t, (Timestamp) value);
        } else {
            System.out.println("SqlError：暂时不支持此数据类型，请使用其他类型代替此类型！");
        }
    }

    /**
     * 分页显示
     * @param lists 要分页的List
     * @param pageSize 每页条目数
     * @param <T>
     * @return 分页后的List
     */
    public static <T> List<List<T>> listLimit(List<T> lists, int pageSize) {
        List<List<T>> llists = new ArrayList<List<T>>();
        for (int i = 0; i < lists.size(); i = i + pageSize) {
            try {
                List<T> list = lists.subList(i, i + pageSize);
                llists.add(list);
            } catch (IndexOutOfBoundsException e) {
                List<T> list = lists.subList(i, i + (lists.size() % pageSize));
                llists.add(list);
            }
        }
        return llists;
    }


}